# /usr/bin/python
# coding: utf-8
import pymysql
import sys
import configparser
import os
from pypinyin import lazy_pinyin, pinyin, Style
import datetime
import time
import util


class dbUtil:

    def __init__(self, dbConfig, table):
        self.table = table
        self.db = pymysql.connect(**dbConfig)
        self.limit = 1
        self.count = 0

    def getDbField(self):
        # 打开数据库连接
        cursor = self.db.cursor()
        sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '%s';" % (
            self.table)
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
            return res
        except Exception as e:
            print("出现错误", repr(e))

    def getRecordCount(self):
        cursor = self.db.cursor()
        sql = "SELECT count(*) from %s;" % (
            self.table)
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
            self.count = res[0][0]
        except Exception as e:
            print("出现错误", repr(e))

    # 将文字转为拼音
    def fieldToPinYin(self, originField, key, targetField, lastUpdateField):
        i = 0
        str = ""
        updateSql = "UPDATE %s " % (
            self.table)
        while True:
            cur = self.db.cursor()
            sql_cmd = 'select {},{},{} from {} limit {}, {}'.format(
                originField, targetField, key, self.table, i * self.limit, self.limit)
            rt = cur.execute(sql_cmd)
            if rt == 0:
                break
            else:
                i += 1
            res = cur.fetchall()
            if res[0][1] == None or res[0][1] == "":
                py = "".join(lazy_pinyin(res[0][0]))
                updateTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                setSql = "SET %s = '%s',%s='%s' where %s='%s';" % (
                    targetField, py, lastUpdateField, updateTime, key, res[0][2])
                tmp = updateSql + setSql
                str += tmp
            cur.close()
        util.writeFile('field_to_full_pinyin.txt', str, '将文字转为拼音', '-'*20)

    # 将文字转为简拼
    def fieldToFirstLetter(self, originField, key, targetField, lastUpdateField):
        i = 0
        str = ""
        updateSql = "UPDATE %s " % (
            self.table)
        while True:
            cur = self.db.cursor()
            sql_cmd = 'select {},{},{} from {} limit {}, {}'.format(
                originField, targetField, key, self.table, i * self.limit, self.limit)
            rt = cur.execute(sql_cmd)
            if rt == 0:
                break
            else:
                i += 1
            res = cur.fetchall()
            if res[0][1] == None or res[0][1] == "":
                py = "".join(lazy_pinyin(res[0][0], style=Style.FIRST_LETTER))
                updateTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                setSql = "SET %s = '%s' ,%s='%s' where %s='%s';" % (
                    targetField, py, lastUpdateField, updateTime, key, res[0][2])
                tmp = updateSql + setSql
                str += tmp
            cur.close()
        util.writeFile('field_to_single_pinyin.txt', str, '将文字转为简拼', '-'*20)

    # 将身份证转为生日
    def fieldToBirthday(self, originField, key, targetField, lastUpdateField):
        i = 0
        notHaveIdCardNum = 0
        illegelIdCardNum = 0
        str = ""
        updateSql = "UPDATE %s " % (
            self.table)
        while True:
            cur = self.db.cursor()
            sql_cmd = 'select {},{},{} from {} limit {}, {}'.format(
                originField, targetField, key, self.table, i * self.limit, self.limit)
            rt = cur.execute(sql_cmd)
            if rt == 0:
                break
            else:
                i += 1
            res = cur.fetchall()
            if res[0][1] == None or res[0][1] == "":
                id = res[0][0]
                USER_ID = res[0][2]
                if id != None and id != "":
                    if util.checkIdCard(id):
                        birth_year = id[6:10]
                        birth_month = id[10:12]
                        birth_day = id[12:14]
                        birthday = birth_year+"-"+birth_month+"-"+birth_day
                        updateTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        setSql = "SET %s = '%s',%s='%s' where %s='%s';" % (
                            targetField, birthday, lastUpdateField, updateTime, key, USER_ID)
                        tmp = updateSql + setSql
                        str += tmp
                    else:
                        illegelIdCardNum += 1
                        continue
                else:
                    notHaveIdCardNum += 1
            cur.close()
        util.writeFile('field_to_birthday.txt', str, '将身份证转为生日', "共%d条记录未获取到身份证:" %
                       notHaveIdCardNum, '共%d条非法身份证' % illegelIdCardNum, '-'*20)
